package org.a1.ectltool.control;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import org.a1.ectltool.boundary.MainFrame;
import org.a1.ectltool.entity.Product;
import org.a1.ectltool.entity.Sales;
import org.a1.ectltool.entity.SuperMarket;
import org.a1.ectltool.entity.Time;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class KoffieBestandEuro20082009ExcelReader implements FileReader
{
	private static final int SUPERMARKET_ALBERT_HEIJN = 3189;
	private static final int SUPERMARKET_SUPER_DE_BOER = 6378;
	private static final int SUPERMARKET_C1000 = 9567;
	private static final int SUPERMARKET_SUPERUNIE = 12756;
	private static final int SUPERMARKET_COOP_TOTAAL = 15945;
	private static final int SUPERMARKET_HOOGVLIET = 19134;
	private static final int SUPERMARKET_JUMBO = 22323;
	private static final int SUPERMARKET_VOMAR = 25512;
	private static final int SUPERMARKET_JAN_LINDERS = 28701;
	private static final int SUPERMARKET_DEEN = 31890;
	private static final int SUPERMARKET_BONIMARKT = 35079;
	private static final int SUPERMARKET_PLUS = 38268;
	
	private File file;
	
	private HashMap<Integer, String> types;
	
	private ArrayList<Sales> sales;
	
	private int current_market = 0;
	
	public KoffieBestandEuro20082009ExcelReader(File file)
	{
		this.file = file;
		types = new HashMap<Integer, String>();
		sales = new ArrayList<Sales>();
		MainFrame.getFrame().addLog("new KoffieBestandEuro20082009ExcelReader(" + file.getName() + ")");
	}
	
	@Override
	public void extract()
	{
		MainFrame.getFrame().addLog("Extracting...");
		long msStart = System.currentTimeMillis();
		
		FileInputStream fis = null;
		try
		{
			MainFrame.getFrame().addLog("Reading file...");
			
			fis = new FileInputStream(file);
			
			//Get the workbook instance for XLS file 
			XSSFWorkbook workbook = new XSSFWorkbook(fis);

			//Get first sheet from the workbook
			XSSFSheet sheet = workbook.getSheetAt(1);

			//Get iterator to all the rows in current sheet
			Iterator<Row> rowIterator = sheet.iterator();
			
			Product product;
			SuperMarket superMarket = null;
			Time time;
			
			String brand ="";
			String machine = "";
			String type = "";
			String experience = "";
			String kind = "";
			String size = "";
			String flavour = "";
			String uPC = "";
			
			while(rowIterator.hasNext())
			{
				Row row = rowIterator.next();
				
				if(row.getRowNum() == SUPERMARKET_ALBERT_HEIJN)
				{
					superMarket = new SuperMarket("Albert Heijn");
					current_market = SUPERMARKET_ALBERT_HEIJN;
				}
				else if(row.getRowNum() == SUPERMARKET_BONIMARKT)
				{
					superMarket = new SuperMarket("Bonimarkt");
					current_market = SUPERMARKET_BONIMARKT;
				}
				else if(row.getRowNum() == SUPERMARKET_C1000)
				{
					superMarket = new SuperMarket("C1000");
					current_market = SUPERMARKET_C1000;
				}
				else if(row.getRowNum() == SUPERMARKET_COOP_TOTAAL)
				{
					superMarket = new SuperMarket("COOP Totaal");
					current_market = SUPERMARKET_COOP_TOTAAL;
				}
				else if(row.getRowNum() == SUPERMARKET_DEEN)
				{
					superMarket = new SuperMarket("Deen");
					current_market = SUPERMARKET_DEEN;
				}
				else if(row.getRowNum() == SUPERMARKET_HOOGVLIET)
				{
					superMarket = new SuperMarket("Hoogvliet");
					current_market = SUPERMARKET_HOOGVLIET;
				}
				else if(row.getRowNum() == SUPERMARKET_JAN_LINDERS)
				{
					superMarket = new SuperMarket("Jan Linders");
					current_market = SUPERMARKET_JAN_LINDERS;
				}
				else if(row.getRowNum() == SUPERMARKET_JUMBO)
				{
					superMarket = new SuperMarket("Jumbo");
					current_market = SUPERMARKET_JUMBO;
				}
				else if(row.getRowNum() == SUPERMARKET_PLUS)
				{
					superMarket = new SuperMarket("Plus");
					current_market = SUPERMARKET_PLUS;
				}
				else if(row.getRowNum() == SUPERMARKET_SUPER_DE_BOER)
				{
					superMarket = new SuperMarket("Super de Boer");
					current_market = SUPERMARKET_SUPER_DE_BOER;
				}
				else if(row.getRowNum() == SUPERMARKET_SUPERUNIE)
				{
					superMarket = new SuperMarket("Superunie");
					current_market = SUPERMARKET_SUPERUNIE;
				}
				else if(row.getRowNum() == SUPERMARKET_VOMAR)
				{
					superMarket = new SuperMarket("Vomar");
					current_market = SUPERMARKET_VOMAR;
				}
				
				if(row.getRowNum() < SUPERMARKET_ALBERT_HEIJN -1 && row.getRowNum() >= 4)
				{
					if(row.getCell(0) != null)
						types.put(row.getRowNum(), row.getCell(0).getStringCellValue());
				}
				else
				{
					if(row.getRowNum() - current_market + 2 >= 0 && types.get(row.getRowNum() - current_market + 2) != null)
					{
						String tempType = types.get(row.getRowNum() - current_market + 2);
						
						if(tempType.equals("MAJOR.BRAND"))
						{
							String tmp = row.getCell(1).getStringCellValue().replace("   ", "");
							brand = tmp.substring(0, tmp.length() - 1);
						}
						else if(tempType.equals("APPARAAT"))
						{
							String tmp = row.getCell(1).getStringCellValue().replace(brand + " ", "");
							tmp = tmp.replace("   ", "");
							machine = tmp.substring(0, tmp.length() - 1);
						}
						else if(tempType.equals("TYPE"))
						{
							String tmp = row.getCell(1).getStringCellValue().replace(brand + " ", "");
							tmp = tmp.replace("   ", "");
							tmp = tmp.replace(machine + " ", "");
							type = tmp.substring(0, tmp.length() - 1);
						}
						else if(tempType.equals("BELEVING"))
						{
							String tmp = row.getCell(1).getStringCellValue().replace(brand + " ", "");
							tmp = tmp.replace("   ", "");
							tmp = tmp.replace(machine + " ", "");
							tmp = tmp.replace(type + " ", "");
							experience = tmp.substring(0, tmp.length() - 1);
						}
						else if(tempType.equals("SOORT"))
						{
							String tmp = row.getCell(1).getStringCellValue().replace(brand + " ", "");
							tmp = tmp.replace("   ", "");
							tmp = tmp.replace(machine + " ", "");
							tmp = tmp.replace(type + " ", "");
							tmp = tmp.replace(experience + " ", "");
							kind = tmp.substring(0, tmp.length() - 1);
						}
						else if(tempType.equals("FORMAAT"))
						{
							String tmp = row.getCell(1).getStringCellValue().replace(brand + " ", "");
							tmp = tmp.replace("   ", "");
							tmp = tmp.replace(machine + " ", "");
							tmp = tmp.replace(type + " ", "");
							tmp = tmp.replace(experience + " ", "");
							tmp = tmp.replace(kind + " ", "");
							size = tmp.substring(0, tmp.length() - 1);
						}
						else if(tempType.equals("SMAAK"))
						{
							String tmp = row.getCell(1).getStringCellValue().replace(brand + " ", "");
							tmp = tmp.replace("   ", "");
							tmp = tmp.replace(machine + " ", "");
							tmp = tmp.replace(type + " ", "");
							tmp = tmp.replace(experience + " ", "");
							tmp = tmp.replace(kind + " ", "");
							tmp = tmp.replace(size + " ", "");
							if(!tmp.equals(""))
								flavour = tmp.substring(0, tmp.length() - 1);
						}
						else if(tempType.equals("UPC"))
						{
							String tmp = row.getCell(1).getStringCellValue();
							
							int st = tmp.indexOf("(");
							int en = tmp.indexOf(")");
							
							if(st >= 0 && en >= 0)
							{
								uPC = tmp.substring(st + 1, en);
								
								product = new Product(brand, machine, experience, type, kind, size, flavour, uPC);
								
								double priceOne = 0.0;
								double priceTwo = 0.0;
								double priceThree = 0.0;
								double priceFour = 0.0;
								double priceFive = 0.0;
								double priceSix = 0.0;
								
								try
								{
									priceOne = Double.parseDouble(row.getCell(2).getNumericCellValue() + "");
									priceTwo = Double.parseDouble(row.getCell(3).getNumericCellValue() + "");
									priceThree = Double.parseDouble(row.getCell(4).getNumericCellValue() + "");
									priceFour = Double.parseDouble(row.getCell(5).getNumericCellValue() + "");
									priceFive = Double.parseDouble(row.getCell(6).getNumericCellValue() + "");
									priceSix = Double.parseDouble(row.getCell(7).getNumericCellValue() + "");
								}
								catch(Exception e)
								{
									e.printStackTrace();
								}
								
								time = new Time(2008, 1);
								this.sales.add(new Sales(priceOne, superMarket, time, product));
								
								time = new Time(2008, 2);
								this.sales.add(new Sales(priceTwo, superMarket, time, product));
								
								time = new Time(2008, 3);
								this.sales.add(new Sales(priceThree, superMarket, time, product));
								
								time = new Time(2008, 4);
								this.sales.add(new Sales(priceFour, superMarket, time, product));
								
								time = new Time(2009, 1);
								this.sales.add(new Sales(priceFive, superMarket, time, product));
								
								time = new Time(2009, 2);
								this.sales.add(new Sales(priceSix, superMarket, time, product));
							}
						}
					}
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			if(fis != null)
			{
				try
				{
					fis.close();
				}
				catch(Exception e)
				{
					e.printStackTrace();
				}
			}
		}
		
		long msEnd = System.currentTimeMillis();
		MainFrame.getFrame().addLog("Done extracting!");
		MainFrame.getFrame().addLog("Extracted in " + (msEnd - msStart) + " milliseconds");
	}

	@Override
	public void clean()
	{
		MainFrame.getFrame().addLog("Cleaning...");
		long msStart = System.currentTimeMillis();
		long msEnd = System.currentTimeMillis();
		MainFrame.getFrame().addLog("Done cleaning!");
		MainFrame.getFrame().addLog("Cleaned in " + (msEnd - msStart) + " milliseconds");
	}

	@Override
	public void transform()
	{
		MainFrame.getFrame().addLog("Transforming...");
		long msStart = System.currentTimeMillis();
		long msEnd = System.currentTimeMillis();
		MainFrame.getFrame().addLog("Done Transforming!");
		MainFrame.getFrame().addLog("Transformed in " + (msEnd - msStart) + " milliseconds");
	}

	@Override
	public void load()
	{
		MainFrame.getFrame().addLog("Loading...");
		long msStart = System.currentTimeMillis();
		
		DWHManager.getManager().addSales(sales);
		
		long msEnd = System.currentTimeMillis();
		MainFrame.getFrame().addLog("Done loading!");
		MainFrame.getFrame().addLog("Loaded in " + (msEnd - msStart) + " milliseconds");
	}

}
